IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MergeUsers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MergeUsers]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
The spoc writes to PersonMergeHistory table before any AdmPersonId updates are performed
so that we undo the changes incase we make any undesirable updates.
The table maintains the following columns

Column name			Description
-----------			-----------
NewPersonId			The new value (post-update value)
OldPersonId			The old vaue (pre-update value)
TableName			The name of the table where the update occured
IdValue				The value of the identity column for the updated row
Comments			The comment associated with the update [Nullable]
DateCreated			The date the update was performed.


Note:
-----------
The stored procedure is expected to work for merges that happen between existing
Internal Persons and External persons (The latter merged into the former). In which
case we do not have to worry module specific tables where AdmPersonIds are found. Only
the common info about people is updated.
*/

create procedure [dbo].[MergeUsers]
(	
	@goodAdmPersonId int,
	@badAdmPersonId int,
	@comments text = null
)
as
	begin

	set nocount on
	
	declare @errorMessage varchar(255)
	declare @externalPersonTypeId int
	declare @internalPersonTypeId int
	declare @badAdmPersonTypeId int
	declare @goodAdmInternalPersonId int
	declare @badAdmInternalPersonId int
	declare @goodPersonDomainUserId int
	declare @badPersonDomainUserId int
	set @externalPersonTypeId = 2
	set @internalPersonTypeId = 1
	
	if  @goodAdmPersonId = @badAdmPersonId
	begin
		raiserror('An attempt to merge identical users was detected', 16, 1)
		return
	end

	if not exists (select * from AdmPerson where [Id] = @goodAdmPersonId and AdmPersonTypeId = @internalPersonTypeId)
	begin
		raiserror('AdmPersonId for good user not found AND/OR is not internal', 16, 1)
		return
	end
	
	if not exists (select * from AdmPerson where [Id] = @badAdmPersonId)
	begin
		raiserror('AdmPersonId for bad user not found', 16, 1)
		return
	end
	
	set @badAdmPersonTypeId = (select AdmPersonTypeId from AdmPerson where id = @badAdmPersonId)
	set @goodPersonDomainUserId = (select FwkDomainUserId from AdmPerson where id = @goodAdmPersonId)
	set @badPersonDomainUserId = (select FwkDomainUserId from AdmPerson where id = @badAdmPersonId)
	
	if @badAdmPersonTypeId = @internalPersonTypeId
	begin
		set @goodAdmInternalPersonId = (select Id from AdmInternalPerson where AdmPersonId = @goodAdmPersonId)
		set @badAdmInternalPersonId = (select Id from AdmInternalPerson where AdmPersonId = @badAdmPersonId)
	end

	begin transaction Merge	
	
	--------Demographics---------------------
	
	-- begin AdmEmailAddress
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'AdmEmailAddress', Id, @comments
	from	AdmEmailAddress
	where	AdmPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for AdmEmailAddress table failed'
		goto ERROR_HANDLER
	end 

	update	AdmEmailAddress
	set		AdmPersonId = @goodAdmPersonId
	where	AdmPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating AdmEmailAddress failed'
		goto ERROR_HANDLER
	end
	
	-- end AdmEmailAddress
	
	-- begin AdmPersonAddress

	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'AdmPersonAddress', Id, @comments
	from	AdmPersonAddress
	where	AdmPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for AdmPersonAddress table failed'
		goto ERROR_HANDLER
	end 

	update	AdmPersonAddress
	set		AdmPersonId = @goodAdmPersonId
	where	AdmPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating AdmPersonAddress failed'
		goto ERROR_HANDLER
	end
	
	-- end AdmPersonAddress
	
	-- begin AdmPersonLocation

	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'AdmPersonLocation', Id, @comments
	from	AdmPersonLocation
	where	AdmPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for AdmPersonLocation table failed'
		goto ERROR_HANDLER
	end 

	update	AdmPersonLocation
	set		AdmPersonId = @goodAdmPersonId
	where	AdmPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating AdmPersonLocation failed'
		goto ERROR_HANDLER
	end
	
	-- end AdmPersonLocation
	
	-- begin AdmTelephoneNumber

	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'AdmTelephoneNumber', Id, @comments
	from	AdmTelephoneNumber
	where	AdmPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for AdmTelephoneNumber table failed'
		goto ERROR_HANDLER
	end 

	update	AdmTelephoneNumber
	set		AdmPersonId = @goodAdmPersonId
	where	AdmPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating AdmTelephoneNumber failed'
		goto ERROR_HANDLER
	end
	
	-- end AdmTelephoneNumber
	
	-- begin AdmPerson_FwkDomainOrganization_Map
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'AdmPerson_FwkDomainOrganization_Map', Id, @comments
	from	AdmPerson_FwkDomainOrganization_Map
	where	AdmPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for AdmPerson_FwkDomainOrganization_Map table failed'
		goto ERROR_HANDLER
	end 

	update	AdmPerson_FwkDomainOrganization_Map
	set		AdmPersonId = @goodAdmPersonId
	where	AdmPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating AdmPerson_FwkDomainOrganization_Map failed'
		goto ERROR_HANDLER
	end
	
	-- end AdmPerson_FwkDomainOrganization_Map
	
	-- begin FwkDomainOrganization
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'FwkDomainOrganization', Id, @comments
	from	FwkDomainOrganization
	where	ProtocolAdministratorId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for FwkDomainOrganization table failed'
		goto ERROR_HANDLER
	end 

	update	FwkDomainOrganization
	set		ProtocolAdministratorId = @goodAdmPersonId
	where	ProtocolAdministratorId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating FwkDomainOrganization failed'
		goto ERROR_HANDLER
	end
	
	-- end FwkDomainOrganization
	
	-- begin Organization_Signoff_Map
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'Organization_Signoff_Map', Id, @comments
	from	Organization_Signoff_Map
	where	SignOffPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for Organization_Signoff_Map table failed'
		goto ERROR_HANDLER
	end 

	update	Organization_Signoff_Map
	set		SignOffPersonId = @goodAdmPersonId
	where	SignOffPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating Organization_Signoff_Map.SignOffPersonId failed'
		goto ERROR_HANDLER
	end
	
	update	Organization_Signoff_Map
	set		DelegatePersonId = @goodAdmPersonId
	where	DelegatePersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating Organization_Signoff_Map.DelegatePersonId failed'
		goto ERROR_HANDLER
	end
	
	-- end Organization_Signoff_Map
	
	-- begin Organization_WorkflowRole_Person_Map
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'Organization_WorkflowRole_Person_Map', Id, @comments
	from	Organization_WorkflowRole_Person_Map
	where	PersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for Organization_WorkflowRole_Person_Map table failed'
		goto ERROR_HANDLER
	end 

	update	Organization_WorkflowRole_Person_Map
	set		PersonId = @goodAdmPersonId
	where	PersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating Organization_WorkflowRole_Person_Map failed'
		goto ERROR_HANDLER
	end
	
	-- end Organization_WorkflowRole_Person_Map

	-- begin Protocol
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName,IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'Protocol', Id, @comments
	from	Protocol
	where	PiPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for Protocol table failed'
		goto ERROR_HANDLER
	end 

	update	Protocol
	set		PiPersonId = @goodAdmPersonId
	where	PiPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating Protocol.PiPersonId failed'
		goto ERROR_HANDLER
	end
	
	update	Protocol
	set		ProtocolAdminPersonId = @goodAdmPersonId
	where	ProtocolAdminPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating Protocol.ProtocolAdminPersonId failed'
		goto ERROR_HANDLER
	end
	
	update	Protocol
	set		LastModifierUserId = @goodPersonDomainUserId
	where	LastModifierUserId = @badPersonDomainUserId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating Protocol.LastModifierUserId failed'
		goto ERROR_HANDLER
	end
	
	-- end Protocol 
	
	-- begin ProtocolProcess
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName,IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'ProtocolProcess', Id, @comments
	from	ProtocolProcess
	where	SubmitterId = @badPersonDomainUserId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for ProtocolProcess.SubmitterId table failed'
		goto ERROR_HANDLER
	end 
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName,IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'ProtocolProcess', Id, @comments
	from	ProtocolProcess
	where	LastModifiedByUserId = @badPersonDomainUserId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for ProtocolProcess.LastModifiedByUserId table failed'
		goto ERROR_HANDLER
	end

	update	ProtocolProcess
	set		SubmitterId = @goodPersonDomainUserId
	where	SubmitterId = @badPersonDomainUserId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating ProtocolProcess.SubmitterId failed'
		goto ERROR_HANDLER
	end
	
	update	ProtocolProcess
	set		LastModifiedByUserId = @goodPersonDomainUserId
	where	LastModifiedByUserId = @badPersonDomainUserId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating ProtocolProcess.LastModifiedByUserId failed'
		goto ERROR_HANDLER
	end
		
	-- end ProtocolProcess
	
	-- begin AgrAgreement
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName,IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'AgrAgreement', Id, @comments
	from	AgrAgreement
	where	PiPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for AgrAgreement table failed'
		goto ERROR_HANDLER
	end 

	update	AgrAgreement
	set		PiPersonId = @goodAdmPersonId
	where	PiPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating AgrAgreement.PiPersonId failed'
		goto ERROR_HANDLER
	end
	
	-- end AgrAgreement 
	
	-- begin ClinicalTrialBudget
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName,IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'ClinicalTrialBudget', Id, @comments
	from	ClinicalTrialBudget
	where	PiPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for ClinicalTrialBudget table failed'
		goto ERROR_HANDLER
	end 

	update	ClinicalTrialBudget
	set		PiPersonId = @goodAdmPersonId
	where	PiPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating Protocol.PiPersonId failed'
		goto ERROR_HANDLER
	end
	
	update	ClinicalTrialBudget
	set		OriginatorPersonId = @goodAdmPersonId
	where	OriginatorPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating Protocol.OriginatorPersonId failed'
		goto ERROR_HANDLER
	end
	
	-- end ClinicalTrialBudget

	-- begin PersonTraining
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'PersonTraining', Id, @comments
	from	PersonTraining
	where	AdmPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for PersonTraining table failed'
		goto ERROR_HANDLER
	end 


	update	PersonTraining
	set		AdmPersonId = @goodAdmPersonId
	where	AdmPersonId = @badAdmPersonId

	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonTraining failed'
		goto ERROR_HANDLER
	end
	
	-- end PersonTraining
	
	-- begin ProtocolStaff_Map
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName,IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'ProtocolStaff_Map', Id, @comments
	from	ProtocolStaff_Map
	where	AdmPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for ProtocolStaff_Map table failed'
		goto ERROR_HANDLER
	end
	
	DECLARE @StaffOrgId INT
	set @StaffOrgId = (SELECT PrimaryOrganizationId FROM AdmInternalPerson WHERE AdmPersonId = @goodAdmPersonId)
	
	update	ProtocolStaff_Map
	set		AdmPersonId = @goodAdmPersonId, OrganizationId = @StaffOrgId
	where	AdmPersonId = @badAdmPersonId

	

	if @@error <> 0
	begin
		set @errorMessage = 'Updating ProtocolStaff_Map failed'
		goto ERROR_HANDLER
	end
		
	-- end ProtocolStaff_Map
	
	-- begin AdmPerson_RecomAbstract_Map
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName,IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'AdmPerson_RecomAbstract_Map', Id, @comments
	from	AdmPerson_RecomAbstract_Map
	where	AdmPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for AdmPerson_RecomAbstract_Map table'
		goto ERROR_HANDLER
	end
	
	update	AdmPerson_RecomAbstract_Map
	set		AdmPersonId = @goodAdmPersonId
	where	AdmPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating AdmPerson_RecomAbstract_Map failed'
		goto ERROR_HANDLER
	end
		
	-- end AdmPerson_RecomAbstract_Map

	-- begin AdmPerson_InfoEdFaculty_Map

	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName,IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'AdmPerson_InfoEdFaculty_Map', AdmPersonId, @comments
	from	AdmPerson_InfoEdFaculty_Map
	where	AdmPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for AdmPerson_InfoEdFaculty_Map table'
		goto ERROR_HANDLER
	end
	
	update	AdmPerson_InfoEdFaculty_Map
	set		AdmPersonId = @goodAdmPersonId
	where	AdmPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating AdmPerson_InfoEdFaculty_Map failed'
		goto ERROR_HANDLER
	end
	-- end
	
	-- begin AdmTitle_AdmPerson_Map
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'AdmTitle_AdmPerson_Map', Id, @comments
	from	AdmTitle_AdmPerson_Map
	where	AdmPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for AdmTitle_AdmPerson_Map table failed'
		goto ERROR_HANDLER
	end 

	update	AdmTitle_AdmPerson_Map
	set		AdmPersonId = @goodAdmPersonId
	where	AdmPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating AdmTitle_AdmPerson_Map failed'
		goto ERROR_HANDLER
	end
	
	-- end AdmTitle_AdmPerson_Map
	
	-- begin AdmDegree_AdmPerson
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'AdmDegree_AdmPerson', Id, @comments
	from	AdmDegree_AdmPerson
	where	AdmPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for AdmDegree_AdmPerson table failed'
		goto ERROR_HANDLER
	end 

	update	AdmDegree_AdmPerson
	set		AdmPersonId = @goodAdmPersonId
	where	AdmPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating AdmDegree_AdmPerson failed'
		goto ERROR_HANDLER
	end
	
	-- end AdmDegree_AdmPerson
	
	-- begin AdmPerson_FwkDomainOrganization_Map
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'AdmPerson_FwkDomainOrganization_Map', Id, @comments
	from	AdmPerson_FwkDomainOrganization_Map
	where	AdmPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for AdmPerson_FwkDomainOrganization_Map table failed'
		goto ERROR_HANDLER
	end 

	update	AdmPerson_FwkDomainOrganization_Map
	set		AdmPersonId = @goodAdmPersonId
	where	AdmPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating AdmPerson_FwkDomainOrganization_Map failed'
		goto ERROR_HANDLER
	end
	
	-- end AdmPerson_FwkDomainOrganization_Map
	
	-- begin SvcEntity_AdmStaffedPerson_Map
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'SvcEntity_AdmStaffedPerson_Map', Id, @comments
	from	SvcEntity_AdmStaffedPerson_Map
	where	AdmPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for SvcEntity_AdmStaffedPerson_Map table failed'
		goto ERROR_HANDLER
	end 

	update	SvcEntity_AdmStaffedPerson_Map
	set		AdmPersonId = @goodAdmPersonId
	where	AdmPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating SvcEntity_AdmStaffedPerson_Map failed'
		goto ERROR_HANDLER
	end
	
	-- end SvcEntity_AdmStaffedPerson_Map
	
	-- begin SvcEntity_AdmAdministrator_Map
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'SvcEntity_AdmAdministrator_Map', Id, @comments
	from	SvcEntity_AdmAdministrator_Map
	where	AdmPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for SvcEntity_AdmAdministrator_Map table failed'
		goto ERROR_HANDLER
	end 

	update	SvcEntity_AdmAdministrator_Map
	set		AdmPersonId = @goodAdmPersonId
	where	AdmPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating SvcEntity_AdmAdministrator_Map failed'
		goto ERROR_HANDLER
	end
	
	-- end SvcEntity_AdmAdministrator_Map
	
	-- begin FwkResource_AdmPerson_Contact_Map
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'FwkResource_AdmPerson_Contact_Map', Id, @comments
	from	FwkResource_AdmPerson_Contact_Map
	where	AdmPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for FwkResource_AdmPerson_Contact_Map table failed'
		goto ERROR_HANDLER
	end 

	update	FwkResource_AdmPerson_Contact_Map
	set		AdmPersonId = @goodAdmPersonId
	where	AdmPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating FwkResource_AdmPerson_Contact_Map failed'
		goto ERROR_HANDLER
	end
	
	-- end FwkResource_AdmPerson_Contact_Map
	
	-- begin AgrFund
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'AgrFund', Id, @comments
	from	AgrFund
	where	ManagerId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for AgrFund table failed'
		goto ERROR_HANDLER
	end 

	update	AgrFund
	set		ManagerId = @goodAdmPersonId
	where	ManagerId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating AgrFund failed'
		goto ERROR_HANDLER
	end
	
	-- end AgrFund
	
	-- begin AgrFund_AgrAgreement_Map
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'AgrFund_AgrAgreement_Map', Id, @comments
	from	AgrFund_AgrAgreement_Map
	where	ManagerId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for AgrFund_AgrAgreement_Map table failed'
		goto ERROR_HANDLER
	end 

	update	AgrFund_AgrAgreement_Map
	set		ManagerId = @goodAdmPersonId
	where	ManagerId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating AgrFund_AgrAgreement_Map failed'
		goto ERROR_HANDLER
	end
	
	-- end AgrFund_AgrAgreement_Map
	
	-- begin AdmInternalPerson_AdmLocation_Map
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'AdmInternalPerson_AdmLocation_Map', Id, @comments
	from	AdmInternalPerson_AdmLocation_Map
	where	AdmInternalPersonId = @badAdmInternalPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for AdmInternalPerson_AdmLocation_Map table failed'
		goto ERROR_HANDLER
	end 

	update	AdmInternalPerson_AdmLocation_Map
	set		AdmInternalPersonId = @goodAdmInternalPersonId
	where	AdmInternalPersonId = @badAdmInternalPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating AdmInternalPerson_AdmLocation_Map failed'
		goto ERROR_HANDLER
	end

	update	PerSalaryTransaction_Src
	set		AdmInternalPersonId = @goodAdmInternalPersonId
	where	AdmInternalPersonId = @badAdmInternalPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating AdmInternalPerson_AdmLocation_Map failed'
		goto ERROR_HANDLER
	end

	-- Copy over the EmployeeIDs if they are there on an existing InternalPerson
	declare @goodRecEmployeeId VARCHAR(12)
	declare @badRecEmployeeId VARCHAR(12)
	set @goodRecEmployeeId = (SELECT EmployeeNumber FROM AdmInternalPerson WHERE Id = @goodAdmInternalPersonId)
	set @badRecEmployeeId = (SELECT EmployeeNumber FROM AdmInternalPerson WHERE Id = @badAdmInternalPersonId)
	
	if (@goodRecEmployeeId is null) 
	BEGIN
		if (@badRecEmployeeId is not null)  
		BEGIN
			UPDATE AdmInternalPerson SET EmployeeNumber = @badRecEmployeeId WHERE Id = @goodAdmInternalPersonId
		END	
	END

	SELECT @badRecEmployeeId

	-- end AdmInternalPerson_AdmLocation_Map
	
	-- begin PerEmployee_Job_Map
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'PerEmployee_Job_Map', Id, @comments
	from	PerEmployee_Job_Map
	where	AdmInternalPersonId = @badAdmInternalPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for PerEmployee_Job_Map table failed'
		goto ERROR_HANDLER
	end 

	update	PerEmployee_Job_Map
	set		AdmInternalPersonId = @goodAdmInternalPersonId
	where	AdmInternalPersonId = @badAdmInternalPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PerEmployee_Job_Map failed'
		goto ERROR_HANDLER
	end
	
	-- end PerEmployee_Job_Map
	
	-- begin AdmPortfolio
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'AdmPortfolio', Id, @comments
	from	AdmPortfolio
	where	AdmPersonId = @badAdmPersonId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for AdmPortfolio table failed'
		goto ERROR_HANDLER
	end 

	update	AdmPortfolio
	set		AdmPersonId = @goodAdmPersonId
	where	AdmPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating AdmPortfolio failed'
		goto ERROR_HANDLER
	end
	
	-- end AdmPortfolio
	
	-- begin AdmRights_Map
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'AdmRights_Map', Id, @comments
	from	AdmRights_Map
	where	FwkDomainUserId = @badPersonDomainUserId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for AdmRights_Map table failed'
		goto ERROR_HANDLER
	end 

	update	AdmRights_Map
	set		FwkDomainUserId = @goodPersonDomainUserId
	where	FwkDomainUserId = @badPersonDomainUserId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating AdmRights_Map failed'
		goto ERROR_HANDLER
	end
	
	-- end AdmRights_Map
	
	-- begin FwkResource_DomainUser_Map
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'FwkResource_DomainUser_Map', Id, @comments
	from	FwkResource_DomainUser_Map
	where	FwkDomainUserId = @badPersonDomainUserId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for FwkResource_DomainUser_Map table failed'
		goto ERROR_HANDLER
	end 

	update	FwkResource_DomainUser_Map
	set		FwkDomainUserId = @goodPersonDomainUserId
	where	FwkDomainUserId = @badPersonDomainUserId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating FwkResource_DomainUser_Map failed'
		goto ERROR_HANDLER
	end
	
	-- end FwkResource_DomainUser_Map

	-- begin COI_Hierarchy_Map
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'COI_Hierarchy_Map', ParentId, @comments
	from	COI_Hierarchy_Map
	where	ChildId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for COI_Hierarchy_Map table failed'
		goto ERROR_HANDLER
	end 

	--1. delete map entries where goodpersonid is the parent and badpersonid is the child and viceversa
	
	Delete From COI_Hierarchy_Map where (ParentId = @goodAdmPersonId and ChildId = @badAdmPersonId) or (ParentId = @badAdmPersonId and ChildId = @goodAdmPersonId)

	if @@error <> 0
	begin
		set @errorMessage = 'Updating for COI_Hierarchy_Map table failed - Delete mapping entries where goodpersonid is the parent and badpersonid is the child and viceversa'
		goto ERROR_HANDLER
	end 



	--2. check if any childId is the goodpersonid, if so, get the corresponding parentid and delete the (parentId, badpersonid) pair
	--		else update the childId  with the goodPersonId where childId is badpersonid
	
	Declare @parentId INT 
	Set @parentId = 0
	Select @parentId = ParentId From COI_Hierarchy_Map Where ChildId = @goodAdmPersonId
	If (IsNULL(@parentId, 0) <> 0)
	Begin
		Delete From COI_Hierarchy_Map where (ParentId = @parentId and ChildId = @badAdmPersonId)
	End
	if @@error <> 0
	begin
		set @errorMessage = 'Updating for COI_Hierarchy_Map table failed - if any childId is the goodpersonid, get the corresponding parentid and delete the (parentId, badpersonid) pair'
		goto ERROR_HANDLER
	end 

	update	COI_Hierarchy_Map
	set	ChildId = @goodAdmPersonId
	where	ChildId = @badAdmPersonId 
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating COI_Hierarchy_Map failed - Update the childId  with the goodPersonId where childId is badpersonid'
		goto ERROR_HANDLER
	end
	-- end COI_Hierarchy_Map
	
	-- begin UserResourceCache
	
	insert into PersonMergeHistory (NewPersonId, OldPersonId, TableName, IdValue, Comments)
	select	@goodAdmPersonId, @badAdmPersonId, 'UserResourceCache', Id, @comments
	from	UserResourceCache
	where	FwkDomainUserId = @badPersonDomainUserId
		
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PersonMergeHistory for UserResourceCache table failed'
		goto ERROR_HANDLER
	end 

	update	UserResourceCache
	set		FwkDomainUserId = @goodPersonDomainUserId
	where	FwkDomainUserId = @badPersonDomainUserId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating UserResourceCache failed'
		goto ERROR_HANDLER
	end
	
	-- end UserResourceCache
	
	if @badAdmPersonTypeId = @externalPersonTypeId
	begin
		insert into PreMergeAdmExternalPerson
			(AdmPersonId
			,Department
			,Institution
			,title)
		select AdmPersonId,
				Department,
				Institution,
				title
		from AdmExternalPerson where AdmPersonId = @badAdmPersonId
	end
     
    if @@error <> 0
	begin
		set @errorMessage = 'Updating PreMergeAdmExternalPerson failed'
		goto ERROR_HANDLER
	end
	
	if @badAdmPersonTypeId = @internalPersonTypeId
	begin	
		insert into PreMergeAdmInternalPerson
			(AdmPersonId
			,EmployeeNumber
			,PrimaryFacilityId
			,PrimaryOrganizationId
			,StartDate
			,EndDate
			,DoNotUpdate)
		select AdmPersonId,
				EmployeeNumber,
				PrimaryFacilityId,
				PrimaryOrganizationId,
				StartDate,
				EndDate,
				DoNotUpdate
		from AdmInternalPerson where AdmPersonId = @badAdmPersonId
	end
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PreMergeAdmInternalPerson failed'
		goto ERROR_HANDLER
	end
	
	delete from AdmExternalPerson where AdmPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Delete from AdmExternalPerson failed'
		goto ERROR_HANDLER
	end	
	
	delete from AdmInternalPerson where AdmPersonId = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Delete from AdmInternalPerson failed'
		goto ERROR_HANDLER
	end	
	
	-- back up Person rows before deletion
	insert into PreMergeAdmPerson
           (AdmPersonId
           ,FirstName
           ,LastName
           ,Prefix
           ,Suffix
           ,MiddleName
           ,Country
           ,Gender
           ,BirthDate
           ,FwkDomainUserId
           ,AdmPersonTypeId
           ,MergeToId
           ,InsertedDate
           ,FeedName)
     select Id
      ,FirstName
      ,LastName
      ,Prefix
      ,Suffix
      ,MiddleName
      ,Country
      ,Gender
      ,BirthDate
      ,FwkDomainUserId
      ,AdmPersonTypeId
      ,MergeToId
      ,InsertedDate
      ,FeedName
	from AdmPerson
	where [Id] = @badAdmPersonId
	
	if @@error <> 0
	begin
		set @errorMessage = 'Updating PreMergeAdmPerson failed'
		goto ERROR_HANDLER
	end
	
	delete from AdmPerson where [Id] = @badAdmPersonId

	if @@error <> 0
	begin
		set @errorMessage = 'Delete from AdmPerson failed'
		goto ERROR_HANDLER
	end	

	commit transaction Merge

	return
	
	ERROR_HANDLER:
		rollback transaction Merge
		raiserror(@errorMessage, 16,1)
		return	
end

 

GO
